from datetime import datetime

import xlwt
import csv


def open_csv():
    with open('D:/magnific/CP/huanxuan_CP921_S220231229__FA39-6551_20240102_171429.csv', 'r') as f:
        reader = csv.reader(f)
        # start_index = 35
        # finish_index = 13686
        start_index = 27
        finish_index = 2504
        # data_list = []
        data_list_for_dic = []
        y_coordinate = []
        x_coordinate = []
        data_dict = {}
        count = 0
        for i, row in enumerate(reader):
            if i < start_index:
                continue
            if i > finish_index:
                break
            y_coordinate.append(int(row[7]))
            x_coordinate.append(int(row[6]))
            count += 1
            # dataList.append(row[3] + row[4])
            # dataList.append(row[2])
            data_list_for_dic.append(row[4:8])
        for i in range(len(data_list_for_dic)):
            data_dict[data_list_for_dic[i][2] + "+" + data_list_for_dic[i][3]] = data_list_for_dic[i][0]
        return y_coordinate, x_coordinate, data_dict


# Excel模板
def excel_template():
    # Excel文件名, sheet页名, 表格标题
    file_name_xls = "D:/magnific/CP/huanxuan_CP921_out.xls"
    sheet_name_xls = "sheet"
    y_coordinate, x_coordinate, data_dict = open_csv()
    # value_title = [['标题1', '标题2'], ['100', '1000']]
    for dict in data_dict:
        print(dict)
    # print(data_dict)
    write_excel_xls(file_name_xls, sheet_name_xls, y_coordinate, x_coordinate, data_dict)


def write_excel_xls(path, sheet_name, y_coordinate, x_coordinate, data_dict):
    # index = len(x_coordinate)  # 获取需要写入的列数
    print("xls格式表格开始写入！")
    # 获取当前时间
    start_time = datetime.now()

    workbook = xlwt.Workbook()  # 新建一个工作簿
    # print(data_dict)
    sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)  # 新建一个工作表
    for x in x_coordinate:
        for y in y_coordinate:
            sheet.write(y, x, data_dict.get(str(x) + "+" + str(y), None))
    #
    # for i in range(0, index):
    #     for j in range(0, len(y_coordinate)):
    #         sheet.write(i, j, data_dict[str(x_coordinate) + str(y_coordinate)])  # 像表格中写入数据（对应的行和列）
    formatted_time = start_time.strftime("%Y%m%d-%H%M%S")
    print(formatted_time)
    path = f"D:/magnific/CP/huanxuan_CP921_out{formatted_time}.xls"
    workbook.save(path)  # 保存工作簿
    print("xls格式表格写入数据成功！")
    # 计算时间差
    end_time = datetime.now()
    time_difference = end_time - start_time
    print(f"总共耗时 {time_difference}")
    print(path)


if __name__ == "__main__":
    # print(GetID())
    excel_template()